from common import MemberEntity,SearchFilter
import DatabaseConnection

class MemberData(object):
    def __init__(self):
        pass

    def InsertMember(self,member):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('InsMember',(member.Username,member.Name,member.Gender,member.Email\
                                        ,member.Address,member.BirthDate,member.Phone,member.UserType,member.Password))
        cursor.close()
        conn.commit()
        conn.close()

    def UpdateMember(self,member):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('UpdMember',(member.Username,member.Name,member.Gender,member.Email\
                                        ,member.Address,member.BirthDate,member.Phone,member.UserType,member.Password))
        cursor.close()
        conn.commit()
        conn.close()

    def DeleteMember(self,ID):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('DelMember',(ID,))
        cursor.close()
        conn.commit()
        conn.close()

    #This method either return an object of type MemberEntity or None (if there is no record with this ID)
    def SelectMemberByID(self,ID):
        query = """ SELECT Username,Name,Gender,Email,Address,BirthDate,Phone,RegDate,UserType,Password,UserTypeDesc
                    FROM vwMember
                    WHERE Username='%s'""" % ID

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        if len(result) <> 0:
            return MemberEntity.MemberEntity(username=result[0][0],name=result[0][1],gender=result[0][2],email=result[0][3],address=result[0][4]\
                                             ,birthDate=result[0][5],phone=result[0][6],regDate=result[0][7],userType=result[0][8],password=result[0][9],userTypeDesc=result[0][10])
        else:
            return None

    #This method returns a list of objects of type MemberEntity
    def SelectMemberByFilter(self,searchFilter):
        query = """ SELECT Username,Name,Gender,Email,Address,BirthDate,Phone,RegDate,UserType,Password,UserTypeDesc
                    FROM vwMember"""

        whereClause = searchFilter.GetWhereClause()
        if whereClause.strip()<>"":
            query += " WHERE %s" % whereClause

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(MemberEntity.MemberEntity(username=record[0],name=record[1],gender=record[2],email=record[3],address=record[4]\
                                             ,birthDate=record[5],phone=record[6],regDate=record[7],userType=record[8],password=record[9],userTypeDesc=record[10]))
        return recordSet

    def SelectAllMembers(self):
        query = """ SELECT Username,Name,Gender,Email,Address,BirthDate,Phone,RegDate,UserType,Password,UserTypeDesc
                    FROM vwMember"""

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(MemberEntity.MemberEntity(username=record[0],name=record[1],gender=record[2],email=record[3],address=record[4]\
                                             ,birthDate=record[5],phone=record[6],regDate=record[7],userType=record[8],password=record[9],userTypeDesc=record[10]))
        return recordSet
